package com.xhsd.zlong.parseexcel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;

public class ExcelUtils {

	/**
	 * 将excel的每一行读取为一个有每个单元格组成的Map,然后有这些map组成一个list.<br>
	 * <b>注</b>:<i>只能读取excel 2007</i>
	 * 
	 * @param path excel文件的路径
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 * 
	 */
	public static List<Map<Integer, String>> parse(String path) throws FileNotFoundException, IOException{
		
		List<Map<Integer,String>> result = new ArrayList<Map<Integer,String>>();
		
		HSSFWorkbook wb =  new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(new File(path))));
		
		HSSFSheet sheet = wb.getSheetAt(0);
		
		int rowNumber = sheet.getLastRowNum() + 1;  // 得到总行数-----getLastRowNum()是获取最后一行的index
		
		List<Map<Integer,String>> rows = new ArrayList<Map<Integer,String>>();
		boolean flagRowsAdd = false;				//处理空白行
		
		for(int rowIndex = 0;rowIndex < rowNumber;rowIndex++){
			
			if(flagRowsAdd){
				
				result.addAll(rows);
				
				rows = new ArrayList<Map<Integer,String>>();
				
				flagRowsAdd = false;
			}
			
			HSSFRow row = sheet.getRow(rowIndex);
			
			if(row!=null){
				
				Map<Integer,String> map = new HashMap<Integer, String>();
				
				int cellNumber = row.getLastCellNum() + 1; // 得到总列数-----getLastCellNum()是获取最后一行的index
				
				for(int cellIndex = 0;cellIndex < cellNumber;cellIndex++){
					
					Cell cell = row.getCell(cellIndex);
					
					if(cell != null){
						String value = null;
						if(HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType() && HSSFDateUtil.isCellDateFormatted(cell)){
							
							Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
							
							if(date!=null){
								
								value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
							}
						}else{
							// 设置单元格的类型为CELL_TYPE_STRING
							cell.setCellType(HSSFCell.CELL_TYPE_STRING); 
							
							value = cell.getStringCellValue();
						}
							
						if(value != null && value.length() > 0){
							
							flagRowsAdd = true;
						}
						
						map.put(cellIndex, value);
					}
				}
				
				rows.add(map);
			}
		}
		
		if(flagRowsAdd){
			
			result.addAll(rows);
		}
			
		return result;
	}
}
